也就是|都会_MySQL遵循最左前缀匹配原则！面试官：回去等通知吧

篇首语：本文由编程笔记#小编为大家整理，主要介绍了MySQL遵循最左前缀匹配原则！面试官：回去等通知吧相关的知识，希望对你有一定的参考价值。

△Hollis, 一个对Coding有着独特追求的人△

这是Hollis的第 408 篇原创分享

作者 l Hollis

来源 l Hollis&＃xff08;ID&＃xff1a;hollischuang&＃xff09;

我们都知道&＃xff0c;mysql的Innodb引擎中&＃xff0c;索引是通过B&＃43;树来实现的。不管是普通索引还是联合索引&＃xff0c;都需要构造一个B&＃43;树的索引结构。

那么&＃xff0c;我们都知道普通索引的存储结构中在B&＃43;树的每个非节点上记录的索引的值&＃xff0c;而这棵B&＃43;树的叶子节点上记录的是聚簇索引&＃xff08;主键索引&＃xff09;的值。

如&＃xff1a;

那么&＃xff0c;如果是联合索引的话&＃xff0c;这棵B&＃43;树又是如何存储的呢&＃xff1f;

在联合索引中&＃xff0c;联合索引(name,age)也是一个B&＃43;树&＃xff0c;非叶子节点中记录的是name,age两个字段的值&＃xff0c;叶子节点中记录的是name&＃xff0c;age两个字段以及主键id的值。

在存储的过程中&＃xff0c;如上图所示&＃xff0c;当age不同时&＃xff0c;按照age排序&＃xff0c;当age相同时&＃xff0c;则按照name排序。

所以&＃xff0c;了解了索引的存储结构之后&＃xff0c;我们就很容易理解最左前缀匹配了&＃xff1a;因为索引底层是一个B&＃43;树&＃xff0c;如果是联合索引的话&＃xff0c;在构造B&＃43;树的时候&＃xff0c;会先按照左边的key进行排序&＃xff0c;左边的key相同时再依次按照右边的key排序。

所以&＃xff0c;在通过索引查询的时候&＃xff0c;也需要遵守最左前缀匹配的原则&＃xff0c;也就是需要从联合索引的最左边开始进行匹配&＃xff0c;这时候就要求查询语句的where条件中&＃xff0c;包含最左边的索引的值。

在了解了最左前缀匹配之后&＃xff0c;日常我们在工作中&＃xff0c;经常在简历索引以及查询的时候&＃xff0c;都会基于这个默认的约定进行索引的设计和SQL的优化。

大家都默认MySQL一定是遵循最左前缀匹配的。会认为当一个age,name的联合索引存在时&＃xff0c;如果查询语句中不包含age作为条件&＃xff0c;就一定不走索引。

MySQL一定是遵循最左前缀匹配的&＃xff0c;这句话在以前是正确的&＃xff0c;没有任何毛病。但是在MySQL 8.0中&＃xff0c;就不一定了。

索引跳跃扫描

MySQL 8.0.13 版本中&＃xff0c;对于range查询&＃xff08;什么是range后面会提到)&＃xff0c;引入了索引跳跃扫描&＃xff08;Index Skip Scan&＃xff09;优化&＃xff0c;支持不符合组合索引最左前缀原则条件下的SQL&＃xff0c;依然能够使用组合索引&＃xff0c;减少不必要的扫描。

通过一个例子给大家解释一下&＃xff0c;首先有下面这样一张表&＃xff08;参考了MySQL官网的例子&＃xff0c;但是我做了些改动和优化&＃xff09;&＃xff1a;

通过上面的SQL&＃xff0c;先创建一张t1表&＃xff0c;并把f1,f2两个字段设置为联合索引。之后再向其中插入一些记录。

分别在MySQL 5.7.9和MySQL 8.0.30上执行&＃xff1a;

执行结果如下&＃xff1a;

可以看到&＃xff0c;主要有以下几个区别&＃xff1a;

MySQL 5.7中&＃xff0c;type &＃61; index&＃xff0c;rows&＃61;160&＃xff0c;extra&＃61;Using where;Using index

MySQL 8.0中&＃xff0c;type &＃61; range&＃xff0c;rows&＃61;16&＃xff0c;extra&＃61;Using where;Using index for skip scan

这里面的type指的是扫描方式&＃xff0c;range表示的是范围扫描&＃xff0c;index表示的是索引树扫描&＃xff0c;通常情况下&＃xff0c;range要比index快得多。

从rows上也能看得出来&＃xff0c;使用index的扫描方式共扫描了160行&＃xff0c;而使用range的扫描方式只扫描了16行。

接着&＃xff0c;重点来了&＃xff0c;那就是为啥MySQL 8.0中的扫描方式可以更快呢&＃xff1f;主要是因为Using index for skip scan 表示他用到了索引跳跃扫描的技术。

也就是说&＃xff0c;虽然我们的SQL中&＃xff0c;没有遵循最左前缀原则&＃xff0c;只使用了f2作为查询条件&＃xff0c;但是经过MySQL 8.0的优化以后&＃xff0c;还是通过索引跳跃扫描的方式用到了索引了。

优化原理

在MySQL 8.0.13 及以后的版本中&＃xff0c;SELECT f1, f2 FROM t1 WHERE f2 &＃61; 40;SQL执行过程如下&＃xff1a;

• 获取f1字段第一个唯一值&＃xff0c;也就是f1&＃61;1

• 构造f1&＃61;1 and f2 &＃61; 40&＃xff0c;进行范围查询

• 获取f1字段第二个唯一值&＃xff0c;也就是f1&＃61;2

• 构造f1&＃61;2 and f2 &＃61; 40&＃xff0c;进行范围查询

• 一直扫描完f1字段所有的唯一值&＃xff0c;最后将结果合并返回

也就是说&＃xff0c;最终执行的SQL语句是像下面这样的&＃xff1a;

即&＃xff0c;MySQL的优化器帮我们把联合索引中的f1字段作为查询条件进行查询了。

限制条件

在知道了索引跳跃扫描的执行过程之后&＃xff0c;很多聪明的读者其实就会发现&＃xff0c;这种查询优化比较适合于f1的取值范围比较少&＃xff0c;区分度不高的情况&＃xff0c;一旦f1的区分度特别高的话&＃xff0c;这种查询可能会更慢。

所以&＃xff0c;真正要不要走索引跳跃扫描&＃xff0c;还是要经过MySQL的优化器进行成本预估之后做决定的。

所以&＃xff0c;这种优化一般用于那种联合索引中第一个字段区分度不高的情况。但是话又说回来了&＃xff0c;我们一般不太会把区分度不高的字段放在联合索引的左边&＃xff0c;不过事无绝对&＃xff0c;既然MySQL给了一个优化的方案&＃xff0c;就说明还是有这样的诉求的。

但是&＃xff0c;我们不能依赖他这个优化&＃xff0c;建立索引的时候&＃xff0c;还是优先把区分度高的&＃xff0c;查询频繁的字段放到联合索引的左边。

除此之外&＃xff0c;在MySQL官网中&＃xff0c;还提到索引跳跃扫描还有一些其他的限制条件&＃xff1a;

• 表T至少有一个联合索引&＃xff0c;但是对于联合索引(A,B,C,D)来说&＃xff0c;A和D可以是空的&＃xff0c;但B和C必须是非空的。

• 查询必须只能依赖一张表&＃xff0c;不能多表JOIN

• 查询中不能使用GROUP BY或DISTINCT语句

• 查询的字段必须是索引中的列